package getFundInfo;

import java.io.*;
import java.util.List;

import javax.servlet.*;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.transform.Transformers;

import entities.FundInfo;
import entities.FundNetAssertValue;
import entities.NetAssertValue;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import utils.MysqlSessionFactoryUtils;

@SuppressWarnings("unchecked")
public class FundAnalysisService{

	/**
	 * 通过基金code查询净值
	 * @param fundCode
	 * @return
	 * @throws IOException
	 * @throws ServletException
	 */
	public String getFundByCode(String fundCode) throws IOException, ServletException {
		List<FundNetAssertValue> list = FundUtils.getFundByCode(fundCode);
		JSONArray jsonArray = new JSONArray();
		JSONObject jsonObject = new JSONObject();
		jsonObject.put("fundName", list.get(0).getFundName());
		jsonObject.put("fundShortName", list.get(0).getFundShortName());
		JSONArray tmp = new JSONArray();
		for (FundNetAssertValue value : list) {
			tmp = new JSONArray();
			tmp.add(value.getFundDate());
			tmp.add(value.getNetAssertValue());
			jsonArray.add(tmp);
		}
		jsonObject.put("data", jsonArray);
		return jsonObject.toString();
	}

	/**
	 * 获取所有的基金名字
	 * @return
	 * @throws IOException
	 * @throws ServletException
	 */
	public String getAllFundName() throws IOException, ServletException {
		Session session = MysqlSessionFactoryUtils.getSessionFactory().openSession();
		String sql = "SELECT b.fund_code AS fundCode,b.fund_name AS fundName FROM (SELECT DISTINCT(fund_code) FROM net_assert_value) a,fund_info b WHERE a.fund_code= b.fund_code;";
		Query query = session.createSQLQuery(sql).setResultTransformer(Transformers.aliasToBean(FundNetAssertValue.class));
		List<FundNetAssertValue> list = query.list();
		return JSONArray.fromObject(list).toString();
	}
	
	/**
	 * 所有基金的最大值和最小值的差
	 * @return
	 * @throws IOException
	 * @throws ServletException
	 */
	public String getAllFundDif(String sDate,String eDate) throws IOException, ServletException {
		Session session = MysqlSessionFactoryUtils.getSessionFactory().openSession();
		String sql = "";
		sql = " SELECT a.fund_code as fundCode,(MAX(a.NET_ASSERT_VALUE) -MIN(a.NET_ASSERT_VALUE))dif FROM net_assert_value a GROUP BY a.FUND_CODE ORDER BY dif DESC";
		if(sDate != "" && sDate != null){
			sql = " SELECT a.fund_code as fundCode,(MAX(a.NET_ASSERT_VALUE) -MIN(a.NET_ASSERT_VALUE))dif FROM net_assert_value a   WHERE a.FUND_DATE BETWEEN '"+sDate+"'AND '"+eDate+"' GROUP BY a.FUND_CODE ORDER BY dif DESC";
		}
		Query query = session.createSQLQuery(sql).setResultTransformer(Transformers.aliasToBean(FundNetAssertValue.class));
		List<FundNetAssertValue> list = query.list();
		return JSONArray.fromObject(list).toString();
	}
	
	/**
	 * 基金净值从小到大取前n
	 * @return
	 * @throws IOException
	 * @throws ServletException
	 */
	public String getMinFund(String date,int n) throws IOException, ServletException {
		Session session = MysqlSessionFactoryUtils.getSessionFactory().openSession();
		String query = "SELECT a.FUND_CODE FROM net_assert_value a WHERE a.FUND_DATE ='"+date+"' ORDER BY a.NET_ASSERT_VALUE Limit "+n;
		List<String> list = session.createSQLQuery(query).list();
		JSONArray jsonArray = new JSONArray();
		JSONObject jsonObject = new JSONObject();
		JSONArray tmp = new JSONArray();
		for (String string : list) {
			List<FundInfo> fundInfos = session.createQuery("from FundInfo a where a.fundCode = '"+string+"'").list();
			jsonObject.element("shortName", fundInfos.get(0).getFundShortName());
			jsonObject.element("name", fundInfos.get(0).getFundName());
			List<NetAssertValue> netAssertValues = session.createQuery("from NetAssertValue a where a.fundCode = '"+string+"'").list();
			JSONArray dataArray = new JSONArray();
			for (NetAssertValue value : netAssertValues) {
				tmp = new JSONArray();
				tmp.add(value.getFundDate());
				tmp.add(value.getNetAssertValue());
				dataArray.add(tmp);
			}
			jsonObject.element("data", dataArray);
			jsonArray.add(jsonObject);
			jsonObject = new JSONObject();
		}
		return jsonArray.toString();
	}

	public static void main(String[] args) {
		Session session = MysqlSessionFactoryUtils.getSessionFactory().openSession();
		String sql = "SELECT b.fund_code AS fundCode,b.fund_name AS fundName FROM (SELECT DISTINCT(fund_code) FROM net_assert_value) a,fund_info b WHERE a.fund_code= b.fund_code;";
		Query query = session.createSQLQuery(sql).setResultTransformer(Transformers.aliasToBean(FundNetAssertValue.class));
		System.out.println(query.list().get(0).getClass());
		List<FundNetAssertValue> list = query.list();
		System.out.println(JSONArray.fromObject(list).toString());
		for(int i=0;i<list.size();i++){
			System.out.println(list.get(i).getFundName());
		}
	}
}